package baiwa.rta.dao.Impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.MessageFormat;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import baiwa.rta.dao.RoleDao;
import baiwa.rta.dao.mapping.RoleMapping;
import baiwa.rta.model.DataTableModel;
import baiwa.rta.model.Role;
import baiwa.rta.utils.SecurityUtils;

@Repository
public class RoleDaoImpl implements RoleDao {

    @Autowired
    JdbcTemplate jdbcTemplate;

    private static final Logger logger = Logger.getLogger(RoleDaoImpl.class);

    private static final String TABLE = "ROLE";
    private static final String SQL_QUERY = " SELECT * FROM "+ TABLE +" WHERE 1=1  ";
    private static final String SQL_QUERY_BY_ID = " SELECT * FROM "+ TABLE +" WHERE ROLID = ? ";
//    private static final String SQL_QUERY_BY_USERNAME = "SELECT * FROM "+ TABLE +" WHERE USRNME = ?";
//    private static final String SQL_QUERY_ROLE = " SELECT * FROM "+ TABLE +" , USRROLE WHERE  USRROLE.ROLID = ROLE.ROLID AND USRID = ? ";

    @Override
    public Role findById(Integer id) {
        Role model = null;
        List<Role> lists = jdbcTemplate.query(SQL_QUERY_BY_ID, new Object[]{id}, new RoleMapping());
        if (lists != null && lists.size() == 1) {
            model = lists.get(0);
        }
        return model==null? new Role() : model ;
    }


    @Override
    public DataTableModel<Role> findRoleDataTable(Role role, int page, int perPage) {

    	DataTableModel<Role> dataTableModel = new DataTableModel<Role>();

        String sql_where = "";
        if (StringUtils.isNotBlank(role.getRoleName())) {
            sql_where = sql_where.concat(" AND ROLNME LIKE '%" + role.getRoleName() + "%' ");
        }
        if (StringUtils.isNotBlank(role.getRoleDesc())) {
            sql_where = sql_where.concat(" AND ROLDESC LIKE '%" + role.getRoleDesc()  + "%' ");
        }
        if (StringUtils.isNotBlank(role.getActive())) {
            sql_where = sql_where.concat(" AND ACTIVE = '" + role.getActive() + "' ");
        }

        String sqlLimit = MessageFormat.format(" limit {0},{1} ", new Object[]{page, perPage});

        String sql_query = SQL_QUERY + sql_where + sqlLimit;

        logger.info("## findRoleByCriteria SQL: " + sql_query);

        List<Role> lists = jdbcTemplate.query(sql_query, new Object[]{}, new RoleMapping());

        // ------ For Count Rows -----
        String sqlCount = " SELECT COUNT(1) AS TOTAL FROM "+ TABLE +" WHERE 1=1 " + sql_where;
        Integer count = jdbcTemplate.query(sqlCount, new ResultSetExtractor<Integer>() {
            @Override
            public Integer extractData(java.sql.ResultSet rs) throws SQLException, DataAccessException {
                if (rs.next()) {
                    return rs.getInt("total");
                }
                return null;
            }
        });

        dataTableModel.setItems(lists);
        dataTableModel.setTotal(count);
        dataTableModel.setIndex(page);
        dataTableModel.setLength(perPage);
        return dataTableModel;
    }

	private static final StringBuilder SQL_INSERT_ROLE = new StringBuilder()
			.append(" INSERT INTO `RTA`.`"+ TABLE +"`         ")
			.append("             ( `ROLID`,             ")
			.append("              `ROLNME`,            ")
			.append("              `ROLDESC`,           ")
			.append("              `ACTIVE`,            ")
			.append("              `CREBY`,             ")
			.append("              `CREDAT`,            ")
			.append("              `UPDBY`,             ")
			.append("              `UPDDAT`,            ")
			.append("              `USER_GRANT`,        ")
			.append("              `ACCOUNT_GRANT`,        ")
			.append("              `RESULT_PLAN_GRANT` )    ")
			.append(" VALUES ( NULL , ?, ?, ?, ?   , CURDATE() , ?, CURDATE(), ?, ?, ? )  " );
			
    @Override
    public void create(final Role role) {

        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {

            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(SQL_INSERT_ROLE.toString(), Statement.RETURN_GENERATED_KEYS);
                int i = 0;
                ps.setString(++i, role.getRoleName() == null ? null : role.getRoleName());
                ps.setString(++i, role.getRoleDesc() == null ? null : role.getRoleDesc());
                ps.setString(++i, role.getActive() == null ? null : role.getActive());
                ps.setString(++i, SecurityUtils.getUsername());
                ps.setString(++i, SecurityUtils.getUsername());
                ps.setString(++i, role.getUserGrant() == null ? null : role.getUserGrant());
                ps.setString(++i, role.getAccountGrant() == null ? null : role.getAccountGrant());
                ps.setString(++i, role.getResultPlanGrant() == null ? null : role.getResultPlanGrant());
                return ps;
            }

        }, keyHolder);
        
    }

    @Override
    public void update(Role role) {
    	
    		String update_sql = " UPDATE "+ TABLE +" SET ROLNME=? ,  " +
    				" ROLDESC= ? , " +
    				" ACTIVE= ? , " +
    				" UPDBY= ? , " +
    				" UPDDAT= CURDATE() ," +
    				" USER_GRANT = ? , " +
    				" ACCOUNT_GRANT = ? , " +
    				" RESULT_PLAN_GRANT = ? " +
    				" WHERE  ROLID = ? ;" ;
    		  
    		jdbcTemplate.update(update_sql ,
	                new Object[]{
	                    role.getRoleName(),
	                    role.getRoleDesc(),
	                    role.getActive(),
	                    SecurityUtils.getUsername(),
	                    role.getUserGrant(),
	                    role.getAccountGrant(),
	                    role.getResultPlanGrant(),
	                    role.getRoleId()
	              });
    		
    }


    @Override
    public void delete(Role role) {
    	String SQL_DELETE = "DELETE FROM "+ TABLE +" WHERE ROLID=?";
    	jdbcTemplate.update(SQL_DELETE, new Object[]{role.getRoleId()});

    }


}
